<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');

class siteModel extends CI_Model {

	function getParam() {
		$sql = "select * from param  order by param";
		return $this->result($sql);
	}

	function result($sql, $all="rows") {
		$output = array();
		$rows	= array();
		$rsts	= $this->db->query($sql);

		if ($rsts->num_rows() > 0) {
			$rows = $rsts->result_array();
		}
		$rsts->free_result();

		if($rows) {
			switch ($all) {
				case "row":
				case 10:
					// single row
					$output = $rows[0];
					break;
				case "col":	
				case 11:
					// single first cell
					$key 	= array_keys($rows[0]);
					$output = $rows[0][$key[0]];
					break;
				default:
					$output = $rows;
					break;
			}
			unset($rows);
		}

		return $output;
	}

	function getTarunaStatus($id) {
		$sql = "select * from tbl_tkd where no_tkd = '" . $id . "'"; 
		
		return $this->result($sql);
	}
	
	public  function getMenu() {
		$sql = "SELECT sectionID, title, alias, link 
				FROM section WHERE state = 1 ORDER BY orderby";
		return $this->result($sql);
	}

	public  function getMenuChild($sectionID) {
		$sql = "SELECT categoryID, title, alias, link 
				FROM category WHERE state = 1 and sectionID = '$sectionID' ORDER BY orderby";
		return $this->result($sql);
	}	
	
	public function slideshow(){
		$sql = "SELECT a.contentID, b.alias section, c.alias category, a.title, a.alias, d.img, a.introtext
				FROM content a
				LEFT JOIN category b on a.categoryID  = b.categoryID
				LEFT JOIN section c on b.sectionID = c.sectionID
				LEFT JOIN content_img d on a.contentID = d.contentID and d.img_type like '%slideshow%'
				WHERE a.state=1 and a.slideshow = 1 order by a.created";
		return $this->result($sql);
	}
	
	public function headline(){
		$sql = "SELECT a.contentID, a.created, b.alias section, c.alias category, a.title, a.alias, d.img, a.introtext
				FROM content a
				LEFT JOIN category b on a.categoryID  = b.categoryID
				LEFT JOIN section c on b.sectionID = c.sectionID
				LEFT JOIN content_img d on a.contentID = d.contentID and d.img_type like '%headline%'
				WHERE a.state=1 and a.headline = 1 order by a.created desc limit 0,3";
		return $this->result($sql);
	}	
	
	function contact($post){
		$sql = "INSERT INTO contact (name, email, message) 
				VALUES ('$post[name]', '$post[email]', '$post[message]')";
		$this->db->query($sql);		
	}

	function getReffAngkatan($page) {
    		$sql = "select * from ref_student order by year";
    		return $this->result($sql);	
 		}
   	
   	function getReffPropinsi(){
   			$sql = "select * from ref_propinsi order by code";
   			return $this->result($sql);
   	}
   	
   	function getReffJenisSmta(){
   			$sql = "select * from ref_jenis_smta order by code";
   			return $this->result($sql);
   	}
   	 
   	function getReffJurusanSmta(){
   			$sql = "select * from ref_jurusan_smta order by code";
   			return $this->result($sql);
   	}
   	
   	function getReffJurusan(){
        $sql = "select * from ref_jurusan order by code";
        return $this->result($sql);     
   	}
   	
   	function getReffLokasi(){
        $sql = "select * from ref_lokasi order by code";
        return $this->result($sql);     
   	}
   	
   	function getReffLokasiTKD(){
        $sql = "select * from ref_lokasi_tkd order by code";
        return $this->result($sql);     
   	}
   	
   	function getReffLokasiTKA(){
        $sql = "select * from ref_lokasi_tka order by code";
        return $this->result($sql);     
   	}
   	
   	function getReffAgama(){
        $sql = "select * from ref_agama order by code";
        return $this->result($sql);     
   	}

   	function getReffArtikel() {
   		$sql = "select distinct type  from perpustakaan order by type";
   		return $this->result($sql);
   	}	
		
	function getReffPin(){
        $sql = "select * from pin where state = 0 order by pinID";
        return $this->result($sql);     
   	}

	public function getContent($page){
        $sql = "select a.*, b.fullname from content a
                left join user b on a.userID = b.userID
                where a.alias = '$page'";
        return $this->result($sql, 'row');
    }
	
	function alumni($limit=0, $offset=0, $filter=array()){
        $where = '';
        $_where = '';
        $aField = array('npt', 'nama', 'angkatan', 'lulus', 'jurusan');
        if (count($filter) > 0 ){
            $_filter = array();
            foreach($_POST as $field=>$value) {
                if (in_array($field, $aField)) $_filter[$field] = trim($value);
            }
            $this->session->set_userdata('filter', $_filter);
        }
        if ($this->session->userdata('filter')) {
            $aFilter = $this->session->userdata('filter');
            foreach ($aFilter as $field=>$value) {
                if (strlen($value) > 0) {
                    switch ($field) {
                        case 'npt':
						case 'nama':
                            $_where .= " and $field like '%".$value."%'";
                            break;
                        case 'angkatan':
                        case 'lulus':
							$_where .= " and $field ='".$value."'";
                            break;
                        case 'jurusan':
                                $_where .= " and b.jurusanid ='".$value."'";
                            break;
                    }
                }
            }
            if (strlen($_where) > 0) $where = $_where;
        }
        //echo "<br>$where";
        $col    = $limit ? "a.*, b.jurusan" : "count(*) count";
		$ord    = $limit ? "order by nama " : "";
		$lim    = $limit ? "limit $limit offset $offset" : "";
        $sql    = " SELECT  $col
					FROM 	alumni a
					LEFT JOIN ref_jurusan  b on a.jurusanid  = b.jurusanid
					WHERE 1 = 1 $where  $ord  $lim";
		//echo $sql;			
       return $this->result($sql);
    }

	function progressAlumni(){
		$sql = "select a.*, b.jurusan from progress_alumni a left join ref_jurusan b on a.jurusanID = b.jurusanID order by lulus";
		return $this->result($sql);
	}
	
	/* edited 230810 11:06 */
	function pustaka($limit=0, $offset=0, $filter=array()){
	//print_r($filter);
        $where = '';
        $_where = '';
        $aField = array('title', 'author', 'type', 'publisher');
        if (count($filter) > 0 ){
            $_filter = array();
            foreach($_POST as $field=>$value) {
                if (in_array($field, $aField)) $_filter[$field] = trim($value);
            }
            $this->session->set_userdata('filter', $_filter);
        }
        if ($this->session->userdata('filter')) {
            $aFilter = $this->session->userdata('filter');
            foreach ($aFilter as $field=>$value) {
                if (strlen($value) > 0) {
                    switch ($field) {
                        case 'title':
						case 'author':
                            $_where .= " and $field like '%".$value."%'";
                            break;
                        case 'type':
							$_where .= " and $field ='".$value."'";
                            break;
                    }
                }
            }
            if (strlen($_where) > 0) $where = $_where;
        }
		
        //echo "<br>$where";
        $col    = $limit ? "*" : "count(*) count";
		$ord    = $limit ? "order by title " : "";
		$lim    = $limit ? "limit $limit offset $offset" : "";
        $sql    = " SELECT  $col
					FROM 	perpustakaan
					WHERE 1 = 1 $where  $ord  $lim";
					
       return $this->result($sql);
    }	

	function materiSemester($semester){
		$sql = "SELECT distinct kode, matakuliah, jenis, sks, totalSKS,file  from silabus 
				where parentid is null and jurusanid = 1 and semester='$semester' order by jenis desc, kode";
		return $this->result($sql);
	}
	
	function materiKuliah(){
		$sql = "SELECT DISTINCT a.iddosen, b.nama dosen, c.nama makul, d.file file FROM dosenpengajar a
				  JOIN dosen b ON a.iddosen = b.id
				  JOIN makul c ON a.idmakul = c.id
				  LEFT JOIN silabus d ON c.id = d.kode
				  WHERE IDDOSEN = 777 OR IDDOSEN = 779
				  ORDER BY b.nama LIMIT 0, 16";
				  
		return $this->result($sql);
	}
	
	function mahasiswa(){
		/* $sql = "select a.*, b.nama jurusan from mahasiswa a left join prodi b on a.idprodi = b.id 
				where YEAR(a.tanggalmasuk) in ('2007', '2008', '2009') 
				order by a.angkatan desc, a.nama";
		*/
		$sql = "select a.*, b.nama jurusan from mahasiswa a left join prodi b on a.idprodi = b.id 
				where a.angkatan in ('2007', '2008', '2009') 
				order by a.angkatan desc, a.nama";
		
		return $this->result($sql);
	}
	
	function kelulusan(){
		$sql = "select a.*, b.jurusan from alumni a left join ref_jurusan b on a.jurusanID = b.jurusanID 
				where a.lulus = '2010'";
		return $this->result($sql);
	}

	function berita($limit=5, $offset=0){
        $col    = $limit ? "a.*, b.img" : "count(*) count";
		$ord    = $limit ? "order by created desc " : "";
		$lim    = $limit ? "limit $limit offset $offset" : "";
		$where 	= $limit ? "a.state = 1 and a.categoryID = 13" : "";
        $sql    = " SELECT  $col
					FROM 	content a
					left join content_img b on a.contentID = b.contentID and b.img_type like '%headline%'
					WHERE $where $ord  $lim";
       return $this->result($sql);
    }
	
	function getBeritaDetail($contentID){
        $sql    = " SELECT  a.*
					FROM 	content a
					left join content_img b on a.contentID = b.contentID and b.img_type like '%headline%'
					WHERE a.contentID = '$contentID'";
       return $this->result($sql,'row');
    }	

	function getBeritaImage($contentID){
  			$sql   = "select img from content_img where contentID = $contentID";
  			return $this->result($sql);
  	}
	
	function fasilitas(){
		$sql = "SELECT a.*,a.fulltext,b.img FROM content a
		  LEFT JOIN content_img b on a.contentID = b.contentID AND b.img_type like '%content%'		  
		  where categoryID = 7 AND state = 1 order by title";
		return $this->result($sql);
	}
	
	function getFasilitasDetail($contentID){
        $sql    = " SELECT  a.*, b.img
					FROM 	content a
					left join content_img b on a.contentID = b.contentID and b.img_type like '%content%'
					WHERE categoryID = 7 AND a.contentID = '$contentID'";
       return $this->result($sql,'row');
  }
    
	function gallery(){
		$sql = "SELECT a.*,a.fulltext,b.img FROM content a
		  LEFT JOIN content_img b on a.contentID = b.contentID AND b.img_type like '%content%' 
		  where categoryID = 27 AND state = 1 order by imgID desc";
		return $this->result($sql);
	}
		
	function pejabat(){
		$sql = "SELECT * FROM pejabat
			order by orderby";
		return $this->result($sql);
	}
		
	function dosen(){
		$sql = "SELECT * FROM dosen where ID>=778 AND ID<=796
		order by NAMA";
		return $this->result($sql);
	}	
		
	function getLogin($post) {
		$sql = "
			SELECT *
			FROM T_BILLING T1
			JOIN student_register T2 ON T1.T_BILLING_ID = T2.T_BILLING_ID
			WHERE 
				BILLING_NO ='".$post['username']."' 
				AND PAYEE_ID = '".$post['password']."'
				AND STATE = '1' 
				AND IFNULL(T2.VALID_TO, NOW()+1) > NOW()
			";
		
		return $this->result($sql,"row");
	}
		
	function getProgAlumni($order=null, $sort=null, $limit=0, $offset=0) {
	
		$col = $limit ? "a.*, b.jurusan" : "count(*) count";
		$ord = $limit ? "order by lulus" : "";
		$lim = $limit ? "limit $limit offset $offset" : "";
		$whr = "";
		
		$sql = "SELECT $col FROM progress_alumni a 
			LEFT JOIN ref_jurusan b ON a.jurusanID = b.jurusanID $whr $ord
			$lim";
				   
		return $this->result($sql);
	}
	
	public function getPendaftaranDetail($no_pendaftaran=0){
        $sql = "SELECT a.no_pendaftaran, a.nama, a.jurusan_smtaID, a.jurusanID,
						a.lokasiID, a.tempat_lahir, a.tgl_lahir, a.jenis_kelamin,
						a.tinggi_badan, a.agama, a.propinsiID, a.tahun_lulus, a.jenis_smtaID,
						a.mat_4, a.fisika_4, a.inggris_4,
						a.mat_5, a.fisika_5, a.inggris_5,
						a.mat_6, a.fisika_6, a.inggris_6,
						a.register_date, a.email, a.alamat, a.kodepos, a.telepon, a.foto,
						b.BILLING_NO
				FROM student_register a
				LEFT JOIN T_BILLING b
				ON a.T_BILLING_ID = b.T_BILLING_ID
				where a.no_pendaftaran = '$no_pendaftaran'";
       	return $this->result($sql,"row");
	}
	
	function pinValidation($pin) {
		$sql = "SELECT * FROM pin WHERE pin ='$pin' ";
		return $this->result($sql,"row");
	}
	
	public function getSameEmail($email=""){
        $sql = "SELECT a.email 
				FROM student_register a
				where a.email = '$email'";
        
       	return $this->result($sql,"row");
	}
	
	function getLastID() {
		$sql = "SELECT ID
				FROM    student_register
				WHERE  ID = (SELECT MAX(ID)  FROM student_register)";
		
       	return $this->result($sql,"row");
	}
	
	function setEmptyData() {
		$data = array();
		
		$data['no_pendaftaran'] = "";
		$data['nama'] 			= "";
		$data['tempat_lahir']	= "";
		$data['tgl_lahir_dd']	= "";
		$data['tgl_lahir_mm']	= "";
		$data['tgl_lahir_yy']	= "";
		$data['jenis_kelamin'] 	= "";
		$data['tinggi_badan'] 	= "";
		$data['agama'] 			= "";
		$data['propinsiID'] 	= "";
		$data['tahun_lulus'] 	= "";
		$data['jenis_smtaID'] 	= "";
		$data['jurusan_smtaID'] = "";
		$data['jurusanID'] 		= "";
		$data['lokasiID'] 		= "";
		$data['lokasiTKD'] 		= "";
		$data['lokasiTKA'] 		= "";
		$data['no_tkd']			= "";
		$data['mat_4'] 			= "";
		$data['fisika_4'] 		= "";
		$data['inggris_4'] 		= "";
		$data['mat_5'] 			= "";
		$data['fisika_5'] 		= "";
		$data['inggris_5'] 		= "";
		$data['mat_6'] 			= "";
		$data['fisika_6'] 		= "";
		$data['inggris_6'] 		= "";
		$data['ayah'] 			= "";
		$data['ibu'] 			= "";
		$data['alamat'] 		= "";
		$data['kodepos'] 		= "";
		$data['telepon'] 		= "";
		$data['email'] 			= "";
		
		return $data;
	}	

	function getTarunaDetail($id) 
	{
		$this->db->select('
			a.no_pendaftaran,
			a.nama,
			a.alamat,
			a.kodepos,
			a.telepon,
			a.register_date,
			a.foto
		');
		$this->db->from('student_register a');
		$this->db->where('t_billing_id', $id);
		
		return $this->db->get();
	}



	function getValidBillingNo() 
	{
		$this->db->select('BILLING_NO');
		$this->db->from('T_BILLING');
		$this->db->where('STATE', null);
		$this->db->limit(1);
		
		$sql = 'select BILLING_NO, T_BILLING_ID from T_BILLING where state is null limit 1';
		
		//return $this->db->get();
	    return $this->result($sql,"row");
}
	
	function updateBillingNo($id)
	{
		$data = array(
               'STATE' => '1',
			   'UPDATE_DATE' => now()
            );
		
		$this->db->where('T_BILLING_ID', $id);
		$this->db->update('T_BILLING', $data);
	}

}
?>